package com.gzmu.myprint.mapper;

import com.gzmu.myprint.model.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ArtMapper{
    @Select("SELECT subject FROM subjects WHERE id = 5")
    String SelectSubjectsState();

    @Select("SELECT subject FROM subjects WHERE content= substr(#{registration},1,3)")
    String SelectSubjects(@Param("registration") String registration);

    @Select("SELECT checkcode FROM recorder WHERE groud=#{groud} AND serial=#{serial}")
    String SelectRecorder(Recorder recorder);

    @Select( "select name from student where registration = #{registration}" )
    String selectStudentNameByRegistration(@Param("registration") String registration);

    @Select( "select * from student where sid = #{sid} " )
    Student selectStudentBySid(@Param("sid") int sid);

    @Select( "select * from student where registration = #{registration}" )
    Student selectStudentByRegistration(@Param("registration") String registration);

    @Insert("insert into `user` (`user`.name,`user`.group ,`user`.serial, `user`.ontime) values(#{name},#{group},#{serial},now())")
    @Options(useGeneratedKeys=true, keyProperty="uid", keyColumn="uid")
    int login(User user);

    @Update( "update user set downtime = now() where uid = #{uid} " )
    void logout(@Param("uid") int uid);


    @Insert( "insert into achievement(sid,uid,state,type,achievements,time,broadcasting,impromptu,acquirement," +
            "aviationtalent,appearance,culture,major,story,movies,written,television,interview,judge,total,comment) " +
            "values (#{sid},#{uid},#{state},#{type},#{achievements},now(),#{broadcasting},#{impromptu},#{acquirement}," +
            "#{aviationtalent}, #{appearance}, #{culture}, #{major}, #{story}, #{movies}, #{written}, #{television}, " +
            "#{interview}, #{judge} ,#{total} ,#{comment} )" )
    int insertAchievement(Achievement achievement);

    @Select( "select * from achievement where sid = #{sid} and type=#{type}" )
    List<Achievement> findAllAchievementBySid(@Param("sid") int sid, @Param("type") int type);

    @Select( "select * from achievement where sid = #{sid} and type = #{type} and state = 3" )
    Achievement findReviewScoreBySidAndType(@Param("sid") Long sid, @Param("type") int type);

    @Select( "select * from achievement where aid = #{aid}" )
    Achievement findAchievementByAid(@Param("aid") String aid);

    //1.刚刚用这个账号登过了的
    @Select( "select count(*) from student s , achievement a  where s.registration = #{registration} and s.sid=a.sid and " +
            "a.uid = #{uid} and a.type  = #{type}")
    int selectAchievementByRegAndUid(@Param("registration") String reg, @Param("uid") String uid, @Param("type") int type);

    //2.这个学生的的科目已经有两人登了
    @Select( "select a.uid from student s , achievement a  where s.registration = #{registration} and s.sid=a.sid and " +
            "a.type  = #{type}")
    List<Integer> selectAchievementUidByReg(@Param("registration") String reg, @Param("type") int type);

    @Select("select * from user where uid = #{uid}")
    User findGroupByUid(@Param("uid") int uid);

    @Select( "SELECT s.registration,s.`name` sname,u.`name` uname,sub.id ,a.aid,a.type,a.total,a.time from student s , " +
            "achievement a , `user` u ,subjects sub WHERE a.state = 1 and a.sid = s.sid and  a.uid = u.uid and sub.content " +
            "= SUBSTR(s.registration ,1 , 3) order BY s.registration  limit #{start},#{limit}" )
    List<Abnormal> findAllAbnormalAchievement(@Param("start") int start, @Param("limit") int limit);

    @Select( "SELECT s.registration,s.`name` sname,u.`name` uname,sub.id ,a.aid,a.type,a.total,a.time from student s , " +
            "achievement a , `user` u ,subjects sub WHERE a.state = 0 and a.sid = s.sid and  a.uid = u.uid and sub.content " +
            "= SUBSTR(s.registration ,1 , 3) order BY s.registration  limit #{start},#{limit}" )
    List<Abnormal> findSingleAchievement(@Param("start") int start, @Param("limit") int limit);

    @Select( "SELECT count(*) from student s , achievement a , `user` u ,subjects sub WHERE a.state = 1 and a.sid = s.sid " +
            "and  a.uid = u.uid and sub.content = SUBSTR(s.registration ,1 , 3)" )
    int findAllAbnormalAchievementCount();

    @Select( "SELECT count(*) from student s , achievement a , `user` u ,subjects sub WHERE a.state = 0 and a.sid = s.sid " +
            "and  a.uid = u.uid and sub.content = SUBSTR(s.registration ,1 , 3)" )
    int findSingleAchievementCount();

    //List<ArtMapper> getAbnormal(@Param("start") int start,@Param("limit") int limit);

    @Update( "update achievement set state = #{state} where aid = #{aid}" )
    int setAchievementState(@Param("aid") int aid, @Param("state") int state);

    @Select("SELECT b.achievements FROM achievement a,achievement b WHERE a.aid = #{aid} AND a.sid=b.sid AND a.type=b.type " +
            "AND a.state = 1 AND b.state = 1 AND b.aid <> #{aid}")
    String findOtherAchievements(@Param("aid") String aid);

    @Update( "update subjects set subject=#{subject} where id=#{id}" )
    boolean setExamType(@Param("subject") String subject, @Param("id") int id);

    @Select( "select s.registration,s.name,s.candidate,s.identity,s.gender,a.type,a.total from student s,achievement " +
            "a where s.sid = a.sid and a.state = 3 ORDER BY s.registration ASC limit #{start},#{limit}" )
    List<Score> selectAllScore(@Param("start") int start, @Param("limit") int limit);

    @Select( "select count(*) from student s,achievement a where s.sid = a.sid and a.state = 3" )
    int selectAllScoreCount();

    @Select("select id from subjects where content = substr(#{registration} , 1 ,3)")
    int findSubjectId(@Param("registration") String registration);
}
